# encoding:gbk
import pandas as pd
import logging
import sys
from datetime import datetime
import sqlite3
import time

class SQLiteDB:
    def __init__(self, db_name):
        """初始化数据库连接"""
        self.db_name = db_name
        self.conn = None
        self.cursor = None

    def connect(self):
        """连接到SQLite数据库"""
        self.conn = sqlite3.connect(self.db_name)
        self.cursor = self.conn.cursor()
        print(f"Connected to {self.db_name} database.")

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.commit()
            self.conn.close()
            print(f"Connection to {self.db_name} closed.")

    def create_table(self, table_name, columns):
        """创建表格，传入表名和列的定义"""
        columns_str = ', '.join([f"{col} {dtype}" for col, dtype in columns.items()])
        query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_str});"
        self.cursor.execute(query)
        print(f"Table '{table_name}' created or already exists.")

    def insert_data(self, table_name, data):
        """插入数据，data是一个字典形式的键值对"""
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?' for _ in data])
        query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        self.cursor.execute(query, tuple(data.values()))
        print(f"Data inserted into '{table_name}'.")

    def fetch_all(self, query, params=()):
        """执行查询，返回所有结果"""
        self.cursor.execute(query, params)
        return self.cursor.fetchall()

    def fetch_one(self, query, params=()):
        """执行查询，返回一个结果"""
        self.cursor.execute(query, params)
        return self.cursor.fetchone()

    def update_data(self, table_name, data, condition):
        """更新数据，data是一个字典，condition是更新条件"""
        set_clause = ', '.join([f"{key} = ?" for key in data])
        query = f"UPDATE {table_name} SET {set_clause} WHERE {condition}"
        self.cursor.execute(query, tuple(data.values()))
        print(f"Data in '{table_name}' updated.")

    def delete_data(self, table_name, condition):
        """删除数据，condition是删除条件"""
        query = f"DELETE FROM {table_name} WHERE {condition}"
        self.cursor.execute(query)
        print(f"Data from '{table_name}' deleted.")

def init(ContextInfo):
    """初始化上下文参数"""
    ContextInfo.accID = 'xxxxxxxx'
    ContextInfo.stock_symbol = '513060.SH'
    ContextInfo.db_file = r'C:\logs\qmt.sqlite'
    ContextInfo.order_list = []
    ContextInfo.threshold = 500
    ContextInfo.turning_point_threshold = 0.002  # 
    ContextInfo.drop_threshold = 0.99   # 加拐点网格， 下跌1%买入
    ContextInfo.drop_threshold_1 = 0.995 # 正常网格
    ContextInfo.up_threshold = 1.01     # 加拐点网格， 下跌1%买入
    ContextInfo.up_threshold_1 = 1.005   # 正常网格
    ContextInfo.start = '20250227 09:30:00'
    ContextInfo.end = '20250228 15:00:00'
    ContextInfo.base_price = get_base_price(ContextInfo)
    ContextInfo.min_price = ContextInfo.base_price
    ContextInfo.max_price = ContextInfo.base_price
    ContextInfo.net_grid_data = get_net_grid_data(ContextInfo)

def get_base_price(ContextInfo):
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    # query = "SELECT price FROM base_price where stock = '513090.SH'"
    query = "SELECT price FROM base_price where stock = ?"
    result = db.fetch_one(query,(ContextInfo.stock_symbol,))
    # print(float(result[0]))
    db.close()
    return float(result[0])

def update_base_price(ContextInfo):
    # 更新基准价
    orders = get_trade_detail_data(ContextInfo.accID, 'STOCK', 'ORDER')
    price_list = []
    for obj in orders:
        if '.'.join([obj.m_strInstrumentID, obj.m_strExchangeID]) == ContextInfo.stock_symbol:
            price_list.append(obj.m_dTradedPrice)
    ContextInfo.base_price = price_list[-1]
    ContextInfo.min_price = price_list[-1]
    ContextInfo.max_price = price_list[-1]
    update_logs(ContextInfo, ContextInfo.stock_symbol, f"更新基准价:{ContextInfo.base_price}")
    
    # 保存基准价到数据库
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    update_data = {'price': ContextInfo.base_price}
    # condition = "stock = '513090.SH'"
    # db.update_data('base_price', update_data, condition)
    condition = f"stock = '{ContextInfo.stock_symbol}'"
    db.update_data('base_price', update_data, condition)
    db.close()

def update_logs(ContextInfo, stock:str, data:str):
    # 保存日志到数据库
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    # update_data = {'price': ContextInfo.base_price}
    db.insert_data('logs', {'stock':stock, 'datetime':get_bartime(ContextInfo), 'log':data})
    db.close()

def get_net_grid_data(ContextInfo):
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    query = f"SELECT * FROM '{ContextInfo.stock_symbol}'"
    result = db.fetch_all(query)
    db.close()
    return result

def turning_point_trigger(ContextInfo):  # 拐点触发
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    current_price = round(tick_data[ContextInfo.stock_symbol]['lastPrice'],3)
    # ----------------------下跌行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) < ContextInfo.drop_threshold:
        update_logs(ContextInfo, ContextInfo.stock_symbol, f"拐点触发:下跌行情：最新价：{current_price} 基准价：{ContextInfo.base_price} 最低价：{ContextInfo.min_price}")
        if current_price < ContextInfo.min_price:
            ContextInfo.min_price = current_price
            update_logs(ContextInfo, ContextInfo.stock_symbol, f"拐点触发:持续下跌: 更新最低价：{ContextInfo.min_price}")
        elif current_price > ContextInfo.min_price * (1 + ContextInfo.turning_point_threshold):
            update_logs(ContextInfo, ContextInfo.stock_symbol, f"拐点触发:下跌反弹: 最新价:{current_price} > 最低价:{ContextInfo.min_price} + 最低价:{ContextInfo.min_price} * {ContextInfo.turning_point_threshold}")
            exec_trade(ContextInfo)  # 调用交易函数

    # ----------------------上涨行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) > ContextInfo.up_threshold:
        update_logs(ContextInfo, ContextInfo.stock_symbol, f"拐点触发:上涨行情：最新价:{current_price} 基准价：{ContextInfo.base_price} 最高价：{ContextInfo.max_price}")
        if current_price > ContextInfo.max_price:
            ContextInfo.max_price = current_price
            update_logs(ContextInfo, ContextInfo.stock_symbol, f"拐点触发:持续上涨:更新最高价：{ContextInfo.max_price}")
        elif current_price < ContextInfo.max_price * (1 - ContextInfo.turning_point_threshold):
            update_logs(ContextInfo, ContextInfo.stock_symbol, f"拐点触发:上涨回落:最新价:{current_price} < 最高价:{ContextInfo.max_price} - 最高价:{ContextInfo.max_price} * {ContextInfo.turning_point_threshold}")
            exec_trade(ContextInfo)  # 调用交易函数

def exec_trade(ContextInfo):
    """处理交易逻辑"""
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    current_price = round(tick_data[ContextInfo.stock_symbol]['lastPrice'],3)
    excel_quantity = [item[1] for item in ContextInfo.net_grid_data if item[0] == current_price][0]
    position_data = get_holdings(ContextInfo.accID, 'STOCK')
    current_quantity = position_data[ContextInfo.stock_symbol]['持仓量']
    update_logs(ContextInfo, ContextInfo.stock_symbol, f"交易流程:当前持仓量:{current_quantity} 表格持仓量:{excel_quantity}")
    print(f"-- 交易流程：当前持仓量 {current_quantity}  表格持仓量 {excel_quantity}")
    #--------------------------- 表格持仓量 > 当前持仓量，买入 --------------------------------------#
    if excel_quantity - current_quantity > ContextInfo.threshold:
        account_info = get_account(ContextInfo, ContextInfo.accID, 'STOCK')
        available_cash = account_info.get('可用金额', 0)
        trade_cost = current_price * (excel_quantity - current_quantity)
        if available_cash >= trade_cost:
            stk_price = tick_data[ContextInfo.stock_symbol]['askPrice'][1]
            stk_amount = round((excel_quantity - current_quantity)/100) * 100
            passorder(23, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, stk_price, stk_amount, 1, ContextInfo)
            update_logs(ContextInfo, ContextInfo.stock_symbol, f"交易流程:买入 委托价格:{stk_price} 委托数量:{stk_amount}(Excel {excel_quantity} - 持仓 {current_quantity})")
            print(f"--交易流程--\n委托价格 {stk_price} 委托数量 {stk_amount} (Excel {excel_quantity} - 持仓 {current_quantity})")
            # 下单后休眠30s，检查是否有撤单
            time.sleep(30)
            exec_cancel(ContextInfo)
        else:
            update_logs(ContextInfo, ContextInfo.stock_symbol, f"交易流程:买入 失败:可用资金:{available_cash} 不足，需 {trade_cost}")
            print(f"--交易流程：买入--\n买入失败：可用资金 {available_cash} 不足，需 {trade_cost}")
    #-------------------------------- 表格持仓量 < 当前持仓量，卖出 --------------------------------------#
    if current_quantity - excel_quantity > ContextInfo.threshold:
        tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
        stk_price = tick_data[ContextInfo.stock_symbol]['bidPrice'][1]
        stk_amount = round((current_quantity - excel_quantity)/100) * 100
        passorder(24, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, stk_price, stk_amount, 1, ContextInfo)
        update_logs(ContextInfo, ContextInfo.stock_symbol, f"交易流程:卖出 委托价格:{stk_price} 委托数量:{stk_amount}(持仓 {current_quantity} - Excel {excel_quantity})")
        print(f"--交易流程：卖出--\n委托价格 {ContextInfo.base_price} 委托数量 {stk_amount} (持仓 {current_quantity} - Excel {excel_quantity})")
        # 下单后休眠30s，检查是否有撤单
        time.sleep(30)
        exec_cancel(ContextInfo)

def get_account(ContextInfo, accountid, datatype):
    """获取账户数据（占位实现，需替换为实际接口）"""
    accounts = get_trade_detail_data(accountid, datatype, 'account')  # 假设返回账户详情列表
    result = {}
    for dt in accounts:
        result['总资产'] = dt.m_dBalance
        result['净资产'] = dt.m_dAssureAsset
        result['总市值'] = dt.m_dInstrumentValue
        result['总负债'] = dt.m_dTotalDebit
        result['可用金额'] = dt.m_dAvailable
        result['盈亏'] = dt.m_dPositionProfit
    return result

def get_holdings(accountid, datatype):
    '''
    arg:
        accountid: 账户id, 
        datatype:
            'FUTURE':期货
            'STOCK':股票
            ......
    return:
    {股票名:{'手数':int, '持仓成本':float, '浮动盈亏':float, '可用余额':int}}
    '''
    PositionInfo_dict = {}
    resultlist = get_trade_detail_data(accountid, datatype, 'POSITION')
    for obj in resultlist:
        PositionInfo_dict[obj.m_strInstrumentID + '.' + obj.m_strExchangeID] = {
            '持仓量':obj.m_nVolume,
            '持仓成本':obj.m_dOpenPrice,
            '浮动盈亏':obj.m_dFloatProfit,
            '可用余额':obj.m_nCanUseVolume,
            '成交日期':obj.m_strOpenDate
        }
    return PositionInfo_dict

def exec_cancel(ContextInfo):  # 撤单
    ''' 对未成交的订单执行撤单操作 '''
    orders = get_trade_detail_data(ContextInfo.accID, 'STOCK', 'ORDER')
    for obj in orders:
        if obj.m_strInstrumentID == ContextInfo.stock_symbol[:6]:
            if obj.m_strOrderSysID not in ContextInfo.order_list and obj.m_nVolumeTotal > 0:
                cancel(obj.m_strOrderSysID, ContextInfo.accID, 'STOCK', ContextInfo)
                print(f"委托状态：已撤，委托编号 {obj.m_strOrderSysID}")
            else:
                # 更新基准价
                update_base_price(ContextInfo)
                print(f"委托状态：已成，委托编号 {obj.m_strOrderSysID}")
            ContextInfo.order_list.append(obj.m_strOrderSysID)
    print(ContextInfo.order_list)

def get_bartime(ContextInfo, length=19):
    # 返回时间：get_bartime(ContextInfo,8) = '20250321', get_bartime(ContextInfo) = '20250321000000'
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y-%m-%d %H:%M:%S')
    return(bar_time[:length])

def handlebar(ContextInfo):
    # bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d%H%M%S')
    # test(ContextInfo)
    
    ''' 判断最新价/昨日收盘价，在区间内正常网格交易，超出区间使用拐点网格交易 '''
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    if tick_data[ContextInfo.stock_symbol]['lastPrice']/tick_data[ContextInfo.stock_symbol]['lastClose'] < 1.01 and tick_data[ContextInfo.stock_symbol]['lastPrice']/tick_data[ContextInfo.stock_symbol]['lastClose'] > 0.99:
        current_price = round(tick_data[ContextInfo.stock_symbol]['lastPrice'],3)
        if round((current_price/ContextInfo.base_price), 3) <= ContextInfo.drop_threshold_1 or round((current_price/ContextInfo.base_price), 3) > ContextInfo.up_threshold_1:
            exec_trade(ContextInfo)  # 调用交易函数
        print("普通触发")
    else:
        print("拐点触发")
        turning_point_trigger(ContextInfo)


def test(ContextInfo):
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d')
    bar_time1 = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y-%m-%d %H:%M:%S')
    
    deals = get_trade_detail_data(ContextInfo.accID, 'STOCK', 'DEAL')
    price_list = []
    for obj in deals:
        if obj.m_strInstrumentID == ContextInfo.stock_symbol[:6]:
            price_list.append(obj.m_dPrice)
    print(price_list[0])





